package com.dmall.jdbc.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.junit.Test;

import com.dmall.jdbc.bean.User;
import com.dmall.jdbc.dao.DmallUserDao;
import com.dmall.jdbc.dao.TestDao;

public class DmallService {
	
	private String driver = "org.sqlite.JDBC";
	private String url = "jdbc:sqlite:C:/tmp/sqlite/test.db";
	
	private TestDao testDao = new TestDao();
	private DmallUserDao userDao = new DmallUserDao();

	public void saveTestAndUser(com.dmall.jdbc.bean.Test test, User user) {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url);
			conn.setAutoCommit(false);
			testDao.insertTest(conn, test);
			//int i = 1/0;
			userDao.insertUser(conn, user);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	@Test
	public void testSave() {
		User user = new User();
		user.setLoginName("zhangsan");
		user.setLoginPassword("zhangsan");
		user.setRealName("张三");
		user.setGender(1);
		user.setBirth("1999-12-12");
		
		com.dmall.jdbc.bean.Test test = new com.dmall.jdbc.bean.Test();
		test.setProId(1234567890L);
		test.setName("wangwu");
		test.setBirth("1999-12-12");
		test.setGender(0);
		test.setIntroduction("this is a good boy!");
		
		this.saveTestAndUser(test, user);
	}

	@Test
	public void testSaveUser() {
		User user = new User();
		user.setLoginName("zhangsan");
		user.setLoginPassword("zhangsan");
		user.setRealName("张三");
		user.setGender(1);
		user.setBirth("1999-12-12");
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url);
			conn.setAutoCommit(false);
			userDao.insertUser(conn, user);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	@Test
	public void testExecuteDDL() {
		StringBuilder sb = new StringBuilder();
		//sb.append("CREATE TABLE 'dmall_test' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,'login_name' TEXT NOT NULL);");
		sb.append("ALTER TABLE 'dmall_test' add gender INTEGER;").append("ALTER TABLE 'dmall_test' add birth TEXT;");
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url);
			conn.setAutoCommit(false);
			userDao.executeDDL(conn, sb.toString());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
